SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[spCascadeUpdate]
@tabla varchar(50),
@clave varchar(50)
AS
SET NOCOUNT ON
declare @stm varchar(3500)
declare @tablaR varchar(50)
declare @campoR varchar(50)
declare @campoF varchar(50)

select @stm = 'CREATE TRIGGER update'+@tabla+' ON ['+@tabla+']'+CHAR(10)
select @stm = @stm +  ' FOR UPDATE AS '+CHAR(10)
select @stm = @stm + ' IF (update('+@clave+')) begin '+CHAR(10)
select @stm = @stm + CHAR(9)+' SELECT * FROM inserted '+CHAR(10)
select @stm = @stm + CHAR(9)+' IF @@ROWCOUNT = 1 begin '+CHAR(10)

declare campos cursor for
	Select sysobjects.name, syscolumns.name, syscolumns2.name 
	from sysforeignkeys, sysobjects, syscolumns, syscolumns as syscolumns2
	where rkeyid = object_id(@tabla)
	and fkeyid = sysobjects.id
	and syscolumns.id = fkeyid and
	syscolumns.colid = fkey and
	syscolumns2.id = rkeyid and
	syscolumns2.colid = rkey 

open campos
fetch next from campos into @tablaR, @campoR, @campoF

while @@fetch_status = 0 begin
	select @stm = @stm +CHAR(9)+CHAR(9)+ ' UPDATE '+@tablaR+CHAR(10)
	select @stm = @stm +CHAR(9)+CHAR(9)+ ' SET '+@campoR+' = '+'(SELECT '+@clave+' FROM inserted)'+CHAR(10)
	select @stm = @stm +CHAR(9)+CHAR(9)+ ' WHERE '+@tablaR+'.'+@campoR+' = '+'(SELECT '+@clave+' FROM deleted)'+CHAR(10)
	fetch next from campos into @tablaR, @campoR, @campoF
end

select @stm = @stm +CHAR(9)+ 'end  ELSE begin '+CHAR(10)
select @stm = @stm +CHAR(9)+CHAR(9)+ ' ROLLBACK TRANSACTION '+CHAR(10)
select @stm = @stm + ' end '+CHAR(10)+' end '

print @stm

exec(@stm)

close campos
deallocate campos
GO
